/*
 * Copyright (C) 2008 GeoScheduler Team, as stated on <http://www.geoscheduler.org/authors>.
 * 
 * This file is part of GeoScheduler.
 * 
 * GeoScheduler is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * GeoScheduler is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public License
 * along with GeoScheduler. If not, see <http://www.gnu.org/licenses/>.
 */
package org.geoscheduler.tmp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.geoscheduler.commons.tools.GeoTools;

/**
 * Benchmark of geological information storage database.
 *
 * @author srakyi
 */
public class GeoDbBench {
	
	public static class Record {
		int realLat;
		int realLon;
		int realRad;
		
		int latLo;
		int latHi;
		
		int lonLo;
		int lonHi;
		
		int latIdx;
		int lonIdx;
		byte crop;

		public Record(int lat, int lon) {
			super();
			
			this.realLat = lat;
			this.realLon = lon;
		}

		public Record(int realLat, int realLon, int realRad) {
			super();
			
			this.realLat = realLat;
			this.realLon = realLon;
			this.realRad = realRad;
			
			prepareLatLonAcc();
		}
		
		public void prepareLatLonAcc() {
			// first find out latitude borders
			int latDist = (int)(GeoTools.getLatitudeDistanceInDegrees(this.realLat, this.realLon, this.realRad) * 1000000);
			this.latLo = this.realLat - latDist;
			this.latHi = this.realLat + latDist;
			if (this.latLo % 2 == 1) {
				this.latLo ++;
			}
			if (this.latHi % 2 == 1) {
				this.latHi ++;
			}
			
			// now we're gonna find longitude borders
			int lonDist = (int)(GeoTools.getLongitudeDistanceInDegrees(this.realLat, this.realLon, this.realRad) * 1000000);
			this.lonLo = this.realLon - latDist;
			this.lonHi = this.realLon + latDist;
			if (this.lonLo % 2 == 1) {
				this.lonLo ++;
			}
			if (this.lonHi % 2 == 1) {
				this.lonHi ++;
			}
			
			byte crop1 = 0;
			while (((this.latLo >> crop1) != (this.latHi >> crop1)) && (crop1 < 31)) {
				crop1 ++;
			}
			
			byte crop2 = 0;
			while (((this.lonLo >> crop2) != (this.lonHi >> crop2)) && (crop2 < 31)) {
				crop2 ++;
			}
			
			this.crop = crop1 > crop2 ? crop1 : crop2;
			
			this.latIdx = (this.latLo >> this.crop);// << this.crop);
			this.lonIdx = (this.lonLo >> this.crop);// << this.crop);
		}

		@Override
		public String toString() {
			StringBuilder sb = new StringBuilder();
			
			sb.append("Latitude: ").append(this.realLat).append("\tLongitude: ").append(this.realLon).append("\tRadius: ").append(this.realRad).append("\n");
			
			sb.append("latLo: ").append(GeoTools.E6ToSexagesimal(this.latLo, true)).append("\n");
			sb.append("latHi: ").append(GeoTools.E6ToSexagesimal(this.latHi, true)).append("\n");
			sb.append("lonLo: ").append(GeoTools.E6ToSexagesimal(this.lonLo, false)).append("\n");
			sb.append("lonHi: ").append(GeoTools.E6ToSexagesimal(this.lonHi, false)).append("\n");
			sb.append("final lat idx: ").append(this.latIdx).append("\n");
			sb.append("final lon idx: ").append(this.lonIdx).append("\n");
			sb.append("final crop: ").append(this.crop).append("\n");
			
			return sb.toString();
		}

		public static Record getRandom() {
			int lat = (int)(((Math.random() * 178) - 89) * 1000000);
			int lon = (int)(((Math.random() * 358) - 179) * 1000000);
			int rad = (int)((Math.random() * 10000));
			
			System.out.println("Using lat:" + lat + "; lon:" + lon + "; rad:" + rad);
			
			Record r = new Record(lat,lon, rad);
			
			return r;
		}
		
		public static Record getRandomSmall() {
			int lat = (int)(((Math.random() * 178) - 89) * 1000000);
			int lon = (int)(((Math.random() * 358) - 179) * 1000000);
			
//			System.out.println("Using lat:" + lat + "; lon:" + lon);
			
			Record r = new Record(lat,lon);
			
			return r;
		}
		
		public void store(Connection conn) {
			try {
				Statement stmt = conn.createStatement();
				stmt.executeUpdate("INSERT INTO BENCH_LOCATION (lat, lon, radius_meters, crop, la_idx, lo_idx, lat_lo, lat_hi, lon_lo, lon_hi) VALUES " +
						"("+this.realLat+","+this.realLon+","+this.realRad+","+this.crop+","+this.latIdx+","+this.lonIdx+","+this.latLo+","+this.latHi+","+this.lonLo+","+this.lonHi+")");
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		
	}
	
	public static int getNumHits(Connection conn, Record r) {
		try {
			Statement stmt = conn.createStatement();
			
			StringBuilder sql = new StringBuilder("SELECT count(*) AS cnt FROM BENCH_LOCATION WHERE ((1=2)");
			
			byte crop = 0;
			while (crop < 31) {
				sql.append(" OR (crop = "+crop+" AND la_idx = "+(r.realLat >> crop)+" AND lo_idx = "+(r.realLon >> crop)+")");
				crop ++;
			}
			
			sql.append(") AND (lat_lo < "+r.realLat+") AND (lat_hi > "+r.realLat+") AND (lon_lo < "+r.realLon+") AND (lon_hi > "+r.realLon+")");
			
//			System.out.println(sql.toString());
			
			ResultSet rs = stmt.executeQuery(sql.toString());
			rs.next();
			return rs.getInt("cnt");
		} catch (Exception ex) {
			ex.printStackTrace();
			return 0;
		}
	}
	
	public static int getNumHitsNaive(Connection conn, Record r) {
		try {
			Statement stmt = conn.createStatement();
			
			StringBuilder sql = new StringBuilder("SELECT count(*) AS cnt FROM BENCH_LOCATION WHERE ");
			
			sql.append("(lat_lo < "+r.realLat+") AND (lat_hi > "+r.realLat+") AND (lon_lo < "+r.realLon+") AND (lon_hi > "+r.realLon+")");
			
//			System.out.println(sql.toString());
			
			ResultSet rs = stmt.executeQuery(sql.toString());
			rs.next();
			return rs.getInt("cnt");
		} catch (Exception ex) {
			ex.printStackTrace();
			return 0;
		}
	}

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/geoscheduler", "geoscheduler", "geoscheduler");
		
		// Wenceslas square + 100m
//		Record r = new Record(GeoTools.sexagesimalTo1E6("50°4'47.134\"N"), GeoTools.sexagesimalTo1E6("14°25'47.094\"E"), 100);
		
		// a lot of random values to database
//		for (int i = 0; i < 139977; i ++) {
//			Record r = Record.getRandom();
//			System.out.println(r);
//			r.store(conn);
//		}

		long before;
		long sum = 0;
		long sumNaive = 0;
		
		for (int i = 0; i < 50; i ++) {
			System.out.println(i);
			Record testR = Record.getRandomSmall();
			
			before = System.currentTimeMillis();
			getNumHits(conn, testR);
			sum += (System.currentTimeMillis() - before);
//			System.out.println(getNumHits(conn, testR));
//			System.out.println("took: " + (System.currentTimeMillis() - before));
			
			before = System.currentTimeMillis();
			getNumHitsNaive(conn, testR);
			sumNaive += (System.currentTimeMillis() - before);
//			System.out.println(getNumHitsNaive(conn, testR));
//			System.out.println("naive took: " + (System.currentTimeMillis() - before));
		}
		
		System.out.println("sum: " + sum + "\nsumNaive: " + sumNaive);
	}

}
// SELECT * FROM BENCH_LOCATION WHERE (1=2) OR (crop = 0 AND la_idx = 14994480 AND lo_idx = 30811820) OR (crop = 1 AND la_idx = 14994480 AND lo_idx = 30811820) OR (crop = 2 AND la_idx = 14994480 AND lo_idx = 30811820) OR (crop = 3 AND la_idx = 14994480 AND lo_idx = 30811816) OR (crop = 4 AND la_idx = 14994480 AND lo_idx = 30811808) OR (crop = 5 AND la_idx = 14994464 AND lo_idx = 30811808) OR (crop = 6 AND la_idx = 14994432 AND lo_idx = 30811776) OR (crop = 7 AND la_idx = 14994432 AND lo_idx = 30811776) OR (crop = 8 AND la_idx = 14994432 AND lo_idx = 30811648) OR (crop = 9 AND la_idx = 14994432 AND lo_idx = 30811648) OR (crop = 10 AND la_idx = 14994432 AND lo_idx = 30811136) OR (crop = 11 AND la_idx = 14993408 AND lo_idx = 30810112) OR (crop = 12 AND la_idx = 14991360 AND lo_idx = 30810112) OR (crop = 13 AND la_idx = 14991360 AND lo_idx = 30810112) OR (crop = 14 AND la_idx = 14991360 AND lo_idx = 30801920) OR (crop = 15 AND la_idx = 14974976 AND lo_idx = 30801920) OR (crop = 16 AND la_idx = 14942208 AND lo_idx = 30801920) OR (crop = 17 AND la_idx = 14942208 AND lo_idx = 30801920) OR (crop = 18 AND la_idx = 14942208 AND lo_idx = 30670848) OR (crop = 19 AND la_idx = 14680064 AND lo_idx = 30408704) OR (crop = 20 AND la_idx = 14680064 AND lo_idx = 30408704) OR (crop = 21 AND la_idx = 14680064 AND lo_idx = 29360128) OR (crop = 22 AND la_idx = 12582912 AND lo_idx = 29360128) OR (crop = 23 AND la_idx = 8388608 AND lo_idx = 25165824) OR (crop = 24 AND la_idx = 0 AND lo_idx = 16777216) OR (crop = 25 AND la_idx = 0 AND lo_idx = 0) OR (crop = 26 AND la_idx = 0 AND lo_idx = 0) OR (crop = 27 AND la_idx = 0 AND lo_idx = 0) OR (crop = 28 AND la_idx = 0 AND lo_idx = 0) OR (crop = 29 AND la_idx = 0 AND lo_idx = 0) OR (crop = 30 AND la_idx = 0 AND lo_idx = 0)
// SELECT * FROM BENCH_LOCATION WHERE (1=2) OR (crop = 0 AND la_idx = 14994480 AND lo_idx = 30811820) OR (crop = 1 AND la_idx = 7497240 AND lo_idx = 15405910) OR (crop = 2 AND la_idx = 3748620 AND lo_idx = 7702955) OR (crop = 3 AND la_idx = 1874310 AND lo_idx = 3851477) OR (crop = 4 AND la_idx = 937155 AND lo_idx = 1925738) OR (crop = 5 AND la_idx = 468577 AND lo_idx = 962869) OR (crop = 6 AND la_idx = 234288 AND lo_idx = 481434) OR (crop = 7 AND la_idx = 117144 AND lo_idx = 240717) OR (crop = 8 AND la_idx = 58572 AND lo_idx = 120358) OR (crop = 9 AND la_idx = 29286 AND lo_idx = 60179) OR (crop = 10 AND la_idx = 14643 AND lo_idx = 30089) OR (crop = 11 AND la_idx = 7321 AND lo_idx = 15044) OR (crop = 12 AND la_idx = 3660 AND lo_idx = 7522) OR (crop = 13 AND la_idx = 1830 AND lo_idx = 3761) OR (crop = 14 AND la_idx = 915 AND lo_idx = 1880) OR (crop = 15 AND la_idx = 457 AND lo_idx = 940) OR (crop = 16 AND la_idx = 228 AND lo_idx = 470) OR (crop = 17 AND la_idx = 114 AND lo_idx = 235) OR (crop = 18 AND la_idx = 57 AND lo_idx = 117) OR (crop = 19 AND la_idx = 28 AND lo_idx = 58) OR (crop = 20 AND la_idx = 14 AND lo_idx = 29) OR (crop = 21 AND la_idx = 7 AND lo_idx = 14) OR (crop = 22 AND la_idx = 3 AND lo_idx = 7) OR (crop = 23 AND la_idx = 1 AND lo_idx = 3) OR (crop = 24 AND la_idx = 0 AND lo_idx = 1) OR (crop = 25 AND la_idx = 0 AND lo_idx = 0) OR (crop = 26 AND la_idx = 0 AND lo_idx = 0) OR (crop = 27 AND la_idx = 0 AND lo_idx = 0) OR (crop = 28 AND la_idx = 0 AND lo_idx = 0) OR (crop = 29 AND la_idx = 0 AND lo_idx = 0) OR (crop = 30 AND la_idx = 0 AND lo_idx = 0)
// SELECT * FROM BENCH_LOCATION WHERE (1=2) OR (crop = 0 AND la_idx = 14994480 AND lo_idx = 30811820) OR (crop = 1 AND la_idx = 7497240 AND lo_idx = 15405910) OR (crop = 2 AND la_idx = 3748620 AND lo_idx = 7702955) OR (crop = 3 AND la_idx = 1874310 AND lo_idx = 3851477) OR (crop = 4 AND la_idx = 937155 AND lo_idx = 1925738) OR (crop = 5 AND la_idx = 468577 AND lo_idx = 962869) OR (crop = 6 AND la_idx = 234288 AND lo_idx = 481434) OR (crop = 7 AND la_idx = 117144 AND lo_idx = 240717) OR (crop = 8 AND la_idx = 58572 AND lo_idx = 120358) OR (crop = 9 AND la_idx = 29286 AND lo_idx = 60179) OR (crop = 10 AND la_idx = 14643 AND lo_idx = 30089) OR (crop = 11 AND la_idx = 7321 AND lo_idx = 15044) OR (crop = 12 AND la_idx = 3660 AND lo_idx = 7522) OR (crop = 13 AND la_idx = 1830 AND lo_idx = 3761) OR (crop = 14 AND la_idx = 915 AND lo_idx = 1880) OR (crop = 15 AND la_idx = 457 AND lo_idx = 940) OR (crop = 16 AND la_idx = 228 AND lo_idx = 470) OR (crop = 17 AND la_idx = 114 AND lo_idx = 235) OR (crop = 18 AND la_idx = 57 AND lo_idx = 117) OR (crop = 19 AND la_idx = 28 AND lo_idx = 58) OR (crop = 20 AND la_idx = 14 AND lo_idx = 29) OR (crop = 21 AND la_idx = 7 AND lo_idx = 14) OR (crop = 22 AND la_idx = 3 AND lo_idx = 7) OR (crop = 23 AND la_idx = 1 AND lo_idx = 3) OR (crop = 24 AND la_idx = 0 AND lo_idx = 1) OR (crop = 25 AND la_idx = 0 AND lo_idx = 0) OR (crop = 26 AND la_idx = 0 AND lo_idx = 0) OR (crop = 27 AND la_idx = 0 AND lo_idx = 0) OR (crop = 28 AND la_idx = 0 AND lo_idx = 0) OR (crop = 29 AND la_idx = 0 AND lo_idx = 0) OR (crop = 30 AND la_idx = 0 AND lo_idx = 0)

/*
CREATE TABLE BENCH_LOCATION
(
	id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'PK column ... possible cluster solution must generate generally unique ids',
	lat INTEGER NOT NULL COMMENT 'Latitude value (in 1E6 form)',
	lon INTEGER NOT NULL COMMENT 'Longitude value (in 1E6 form)',
	radius_meters INTEGER COMMENT 'Radius value (in meters)',
	crop TINYINT NOT NULL COMMENT 'Accuracy... name is cut, cause we need to keep WHERE condition in search as short as possible',
    la_idx INTEGER NOT NULL,
    lo_idx INTEGER NOT NULL,
    lat_lo INTEGER,
    lat_hi INTEGER,
    lon_lo INTEGER,
    lon_hi INTEGER,
	PRIMARY KEY (id)
)  COMMENT='Table collecting physical locations for the events'
;

CREATE INDEX GEO_IDX USING BTREE ON BENCH_LOCATION (crop ASC,lo_idx ASC,la_idx ASC);
CREATE INDEX GEO_IDX_2 USING BTREE ON BENCH_LOCATION (lat_lo ASC);
CREATE INDEX GEO_IDX_3 USING BTREE ON BENCH_LOCATION (lat_hi ASC);
CREATE INDEX GEO_IDX_4 USING BTREE ON BENCH_LOCATION (lon_lo ASC);
CREATE INDEX GEO_IDX_5 USING BTREE ON BENCH_LOCATION (lon_hi ASC);
CREATE INDEX GEO_IDX_6 USING BTREE ON BENCH_LOCATION (lat_lo ASC, lat_hi ASC, lon_lo ASC, lon_hi ASC);

DROP INDEX GEO_IDX ON BENCH_LOCATION;
DROP INDEX GEO_IDX_2 ON BENCH_LOCATION;
DROP INDEX GEO_IDX_3 ON BENCH_LOCATION;
DROP INDEX GEO_IDX_4 ON BENCH_LOCATION;
DROP INDEX GEO_IDX_5 ON BENCH_LOCATION;
DROP INDEX GEO_IDX_6 ON BENCH_LOCATION;

*/

/*
 * Current data:
 * 100k x 100km
 * 100k x 1km
 * 100k x 10km
 * 100k x 3km
 * 100k x 7km
 * 500k x 10km
 */
